# Product related functions
from app.utils.database import get_db_cursor

def get_products_by_category(category, page=1, per_page=20, search=''):
    """Get products by category"""
    conn, cursor = get_db_cursor()
    try:
        offset = (page - 1) * per_page
        
        if search:
            cursor.execute(f"""
                SELECT * FROM {category} 
                WHERE LOWER(name) LIKE LOWER(%s)
                ORDER BY product_id
                LIMIT %s OFFSET %s
            """, (f'%{search}%', per_page, offset))
        else:
            cursor.execute(f"""
                SELECT * FROM {category} 
                ORDER BY product_id
                LIMIT %s OFFSET %s
            """, (per_page, offset))
        
        products = [dict(row) for row in cursor.fetchall()]
        
        # Get total count
        if search:
            cursor.execute(f"SELECT COUNT(*) FROM {category} WHERE LOWER(name) LIKE LOWER(%s)", (f'%{search}%',))
        else:
            cursor.execute(f"SELECT COUNT(*) FROM {category}")
        
        total = cursor.fetchone()['count']
        
        return products, total
    finally:
        cursor.close()
        conn.close()

def get_all_products(page=1, per_page=20, search=''):
    """Get all products"""
    conn, cursor = get_db_cursor()
    try:
        all_products = []
        
        # Get products from each category
        categories = ['seafood', 'vegetables', 'meats', 'fruits']
        for category in categories:
            if search:
                cursor.execute(f"""
                    SELECT *, '{category}' as category FROM {category} 
                    WHERE LOWER(name) LIKE LOWER(%s)
                    ORDER BY product_id
                """, (f'%{search}%',))
            else:
                cursor.execute(f"""
                    SELECT *, '{category}' as category FROM {category} 
                    ORDER BY product_id
                """)
            
            products = [dict(row) for row in cursor.fetchall()]
            all_products.extend(products)
        
        # Sort by product name to ensure pagination consistency
        # Add global unique ID for each product for sorting
        for i, product in enumerate(all_products):
            product['global_id'] = i
        all_products.sort(key=lambda x: x.get('name', ''))
        
        # Pagination
        total_products = len(all_products)
        offset = (page - 1) * per_page
        paginated_products = all_products[offset:offset + per_page]
        
        return paginated_products, total_products
    finally:
        cursor.close()
        conn.close()

def get_product_by_id(category, product_id):
    """Get product by ID"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute(f"SELECT * FROM {category} WHERE product_id = %s", (product_id,))
        product = cursor.fetchone()
        return dict(product) if product else None
    finally:
        cursor.close()
        conn.close()

def check_product_stock(category, product_id, quantity):
    """Check if product stock is sufficient"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute(f"SELECT stock FROM {category} WHERE product_id = %s", (product_id,))
        product = cursor.fetchone()
        if not product:
            return False, "Product does not exist"
        if product['stock'] < quantity:
            return False, f"Insufficient stock, current stock: {product['stock']}, required: {quantity}"
        return True, "Sufficient stock"
    finally:
        cursor.close()
        conn.close()

def update_product_stock(category, product_id, quantity):
    """Update product stock"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute(f"""
            UPDATE {category} SET stock = stock - %s 
            WHERE product_id = %s AND stock >= %s
            RETURNING stock
        """, (quantity, product_id, quantity))
        result = cursor.fetchone()
        if not result:
            raise Exception("Insufficient stock, deduction failed")
        conn.commit()
        return result['stock']
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()
